import express from 'express';
import { executeSql, prisma } from '../service/mysql.js';
import { logger, dayjs } from '../tools/index.js';
import { TradeType, TranscationType } from '../service/fund.js';
const router = express.Router();

router.get('/statistics', async (req, res, next) => {
  const accountNo = req.query.accountNo;
  const month = req.query.month;
  const day = req.query.day;
  const bookId = req.query.bookId;
  const tags = req.query.tags ? req.query.tags.split(',') : [];
  const queryTransactionIdList = [];
  if (tags.length > 0) {
    const transactionIdList = await prisma.t_transaction_tags.groupBy({
      by: ['transaction_id'],
      where: {
        book_id: bookId,
        tag: {
          in: tags
        }
      }
    });
    transactionIdList.forEach(row => queryTransactionIdList.push(row.transaction_id));
  }

  if (!bookId) {
    return res.json({
      success: true,
      message: '',
      data: []
    });
  }

  // 按月净收入视图
  const netIncomeStatistics = await executeSql(`SELECT trans_type, DATE_FORMAT(create_time, '%Y-%m-%d') AS days, SUM(trans_amount) AS trans_amount FROM t_trans_end_balance
      WHERE ${bookId ? `book_id = '${bookId}'` : '1 = 1'} AND ${accountNo ? `account_no = '${accountNo}'` : '1 = 1'} AND ${queryTransactionIdList.length > 0 ? `transaction_id IN (${queryTransactionIdList.join(',')})` : '1 = 1'}
    GROUP BY trans_type, days;`);

  // 相关时间点
  let filterDate;
  if (day && day.indexOf(month) > -1) {
    filterDate = day ? dayjs(day, 'YYYY-MM-DD') : dayjs();
  } else if (month) {
    filterDate = dayjs(`${month}-01`, 'YYYY-MM-DD');
  } else {
    filterDate = dayjs();
  }
  const firstDayOfMonth = filterDate.startOf('month').startOf('day');
  const lastDayOfMonth = filterDate.endOf('month').startOf('day');
  const lastMonthFirstDay = filterDate.subtract(1, 'month').startOf('month').startOf('day');
  const lastMonthLastDay = lastMonthFirstDay.add(1, 'month').subtract(1, 'day').startOf('day');
  const currentMonday = filterDate.day() === 0 ? filterDate.subtract(6, 'day').startOf('day') : filterDate.subtract(filterDate.day() - 1, 'day').startOf('day');
  const currentSunday = currentMonday.add(6, 'day').startOf('day');
  const lastWeekMonday = currentMonday.subtract(7, 'day').startOf('day');
  const lastWeekSunday = currentMonday.subtract(1, 'day').startOf('day');
  const minDayByWeekAndMonth = lastMonthFirstDay.isBefore(lastWeekMonday) ? lastMonthFirstDay : lastWeekMonday;
  const maxDayByWeekAndMonth = lastDayOfMonth.isAfter(currentSunday) ? lastDayOfMonth : currentSunday;

  // 按天统计视图
  const statisticsByDate = await getFundStatisticsByDate({ bookId, accountNo, transactionIdList: queryTransactionIdList }, minDayByWeekAndMonth, maxDayByWeekAndMonth);
  const statisticsByDateDetails = await getFundDetailsByDate({ bookId, accountNo, transactionIdList: queryTransactionIdList }, minDayByWeekAndMonth, maxDayByWeekAndMonth);
  const statisticsOfCurrentMonthByDate = statisticsByDate.filter(item => dateInRange(item.dayjsObj, firstDayOfMonth, lastDayOfMonth));
  const statisticsOfLastMonthByDate = statisticsByDate.filter(item => dateInRange(item.dayjsObj, lastMonthFirstDay, lastMonthLastDay));
  const statisticsOfCurrentWeekByDate = statisticsByDate.filter(item => dateInRange(item.dayjsObj, currentMonday, currentSunday));
  const statisticsOfLastWeekByDate = statisticsByDate.filter(item => dateInRange(item.dayjsObj, lastWeekMonday, lastWeekSunday));
  const detailsOfMonth = statisticsByDateDetails.filter(item => dateInRange(item.dayjsObj, firstDayOfMonth, lastDayOfMonth));
  const detailsOfLastMonth = statisticsByDateDetails.filter(item => dateInRange(item.dayjsObj, lastMonthFirstDay, lastMonthLastDay));
  const detailsOfWeek = statisticsByDateDetails.filter(item => dateInRange(item.dayjsObj, currentMonday, currentSunday));
  const detailsOfLastWeek = statisticsByDateDetails.filter(item => dateInRange(item.dayjsObj, lastWeekMonday, lastWeekSunday));


  // 按标签统计视图
  const statisticsByMonthTag = await getFundStatisticsByTag({ bookId, accountNo, transactionIdList: queryTransactionIdList }, firstDayOfMonth, lastDayOfMonth);
  const statisticsByWeekTag = await getFundStatisticsByTag({ bookId, accountNo, transactionIdList: queryTransactionIdList }, currentMonday, currentSunday);

  // 本月预算
  const monthBudgetTotalRow = await prisma.t_transaction.aggregate({
    _sum: {
      trade_amount: true
    },
    where: {
      book_id: bookId,
      create_time: {
        gte: firstDayOfMonth.toDate(),
        lte: lastDayOfMonth.toDate()
      },
      trade_type: TradeType.BUDGET
    }
  })
  const monthBudgetTotal = monthBudgetTotalRow?._sum?.trade_amount || 0;
  const monthExpenseTotal = groupByTransType(statisticsOfCurrentMonthByDate)?.['支出']?.trans_amount || 0;

  return res.json({
    success: true,
    message: '',
    data: {
      netIncome: netIncomeStatistics,
      month: {
        total: groupByTransType(statisticsOfCurrentMonthByDate),
        budget: {
          total: monthBudgetTotal,
          rateOfDecrement: monthBudgetTotal > 0 ? parseInt((monthExpenseTotal * 100 / monthBudgetTotal)) : 100
        },
        byDate: statisticsOfCurrentMonthByDate,
        details: detailsOfMonth,
        detailsOfLastMonth: detailsOfLastMonth,
        byDateOfLastMonth: statisticsOfLastMonthByDate,
        totalOfLastMonth: groupByTransType(statisticsOfLastMonthByDate),
        byTag: statisticsByMonthTag,
      },
      week: {
        total: groupByTransType(statisticsOfCurrentWeekByDate),
        byDate: statisticsOfCurrentWeekByDate,
        details: detailsOfWeek,
        detailsOfLastWeek: detailsOfLastWeek,
        byDateLastWeek: statisticsOfLastWeekByDate,
        totalOfLastWeek: groupByTransType(statisticsOfLastWeekByDate),
        byTag: statisticsByWeekTag,
      }
    }
  });
});

const dateInRange = (date, left, right) => {
  return (date.isAfter(left) || date.isSame(left)) && (date.isBefore(right) || date.isSame(right));
};

const getFundStatisticsByDate = async ({ bookId, accountNo, transactionIdList }, startOfDate, endOfDate) => {
  const items = await executeSql(`select
    t1.trans_type,
    DATE_FORMAT(t1.create_time, '%Y-%m-%d') as days,
    SUM(t1.trans_amount) as trans_amount
  from
    t_trans_end_balance t1
  left join t_transaction t2
  on t1.transaction_id = t2.id
  where t1.book_id = '${bookId}' 
    and t1.create_time >= '${startOfDate.format('YYYY-MM-DD')}' 
    and t1.create_time <= '${endOfDate.format('YYYY-MM-DD')}'
    ${accountNo ? `and t1.account_no = '${accountNo}'` : ''}
    ${transactionIdList.length > 0 ? `and t1.transaction_id in(${transactionIdList.join(',')})` : ''}
    and t2.trade_type IN ('${TranscationType.INCOME}', '${TranscationType.EXPENSE}')
  group by
    t1.trans_type,
    DATE_FORMAT(t1.create_time, '%Y-%m-%d');`);
  const formatItems = items.map(item => {
    return {
      trans_type: item.trans_type,
      days: item.days,
      trans_amount: parseFloat(item.trans_amount)
    };
  });
  const resultItems = [];
  const fillTypes = [TranscationType.INCOME, TranscationType.EXPENSE];
  getDatesBetween(startOfDate, endOfDate).map(dayItem => {
    fillTypes.forEach(type => {
      const matchItem = formatItems.find(item => item.days === dayItem && item.trans_type === type);
      if (matchItem) {
        resultItems.push({
          ...matchItem,
          dayjsObj: dayjs(matchItem.days, 'YYYY-MM-DD')
        });
      } else {
        resultItems.push({
          trans_type: type,
          days: dayItem,
          dayjsObj: dayjs(dayItem, 'YYYY-MM-DD'),
          trans_amount: 0
        });
      }
    });
  });
  return resultItems;
}

const getFundDetailsByDate = async ({ bookId, accountNo, transactionIdList }, startOfDate, endOfDate) => {
  const items = await prisma.t_transaction.findMany({
    where: {
      book_id: bookId,
      create_time: {
        gte: startOfDate.toDate(),
        lte: endOfDate.toDate()
      },
      create_account_no: accountNo || undefined,
      id: transactionIdList.length > 0 ? {
        in: transactionIdList
      } : undefined,
      trade_type: {
        in: [TradeType.INCOME, TradeType.EXPENSE]
      }
    }
  });
  return items.map(item => {
    return {
      trade_type: item.trade_type,
      days: dayjs(item.create_time).format('YYYY-MM-DD'),
      amount: item.trade_amount,
      tags: item.tags.split(','),
      dayjsObj: dayjs(item.create_time),
      remark: item.remark
    }
  });
}

const getFundStatisticsByTag = async ({ bookId, accountNo, transactionIdList }, startOfDate, endOfDate) => {
  const items = await executeSql(`select
    t1.trans_type,
    t2.tag,
    SUM(t1.trans_amount) as trans_amount
  from
    t_trans_end_balance t1
  left join t_transaction_tags t2
  on t1.transaction_id  = t2.transaction_id
  left join t_transaction t3
  on t1.transaction_id = t3.id
  where t1.book_id = '${bookId}' 
    and t1.create_time >= '${startOfDate.format('YYYY-MM-DD')}' 
    and t1.create_time  <= '${endOfDate.format('YYYY-MM-DD')}'
    ${accountNo ? `and t1.account_no = '${accountNo}'` : ''}
    ${transactionIdList.length > 0 ? `and t1.transaction_id in(${transactionIdList.join(',')})` : ''}
    and t3.trade_type IN ('${TradeType.INCOME}', '${TradeType.EXPENSE}')
  group by
    t1.trans_type,
    t2.tag;`);
  return items.map(item => {
    return {
      trans_type: item.trans_type,
      tag: item.tag,
      trans_amount: parseFloat(item.trans_amount)
    };
  })
}

const groupByTransType = arr => arr.reduce((accumulator, current) => {
  if (!accumulator[current.trans_type]) {
    accumulator[current.trans_type] = {
      trans_type: current.trans_type,
      trans_amount: 0
    };
  }

  accumulator[current.trans_type].trans_amount += current.trans_amount;
  return accumulator;
}, {});

const getDatesBetween = (startDate, endDate) => {
  const dates = [];
  let currentDate = startDate;

  while (currentDate.isBefore(endDate) || currentDate.isSame(endDate)) {
    dates.push(currentDate.format('YYYY-MM-DD'));
    currentDate = currentDate.add(1, 'day');
  }

  return dates;
}

export default router;